

/*********************************/
/*                                                                                
/* Create Table1 for Comparators */
/*                                                                                
/*********************************/



libname raw 'G:\Data\Rawdata\707677';

libname common 'G:\Data\Workdata\707677\Common\01Data\01SAS';





/*******************/
/* Load our samples */
/*******************/


/* Applicants for debt relief */


data list;

set common.list;

keep pnr aar dr;

dr=1;

run;



/* Comparators */


data clist;

set common.matching_sample;

keep pnr_national aar dr;

dr=0;

rename pnr_national=pnr;

run;



/* Combine */


data list;

set list clist;

run;




proc sort data=list;

by pnr aar;

run;



proc datasets library=work nolist;

modify list;

attrib _all_ label='';

quit;



data list;

set list;

lid=_n_;

run;




proc freq data=list;

tables dr;

run;





/*********************/
/* Data from idapall */
/*********************/




data idapall;

set raw.idapall;

keep pnr aar alder koen aegte_nr faelle_nr hfpria hfaudd dispon_ny KOEJD KOM LOENMV QAKTIVF_NY05 QPASSIV QPASSIVN PSTILL;

run;



proc datasets library=work nolist;

modify idapall;

attrib _all_ label='';

quit;





proc sql;

create table list as select *

from list as a left join

idapall as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




data alder;

set idapall;

keep pnr aar alder;

if alder~='';

run;



data koen;

set idapall;

keep pnr aar koen;

if koen~='';

run;



proc sort data=alder nodupkey;

by pnr;

run;



proc sort data=koen nodupkey;

by pnr;

run;




proc datasets library=work nolist;

delete idapall;

quit;



proc sort data=list nodupkey;

by lid;

run;




/*****************/
/* Data from bef */
/*****************/



data bef;

set raw.bef;

keep pnr aar koen ANTPERSF ANTBOERNF FOED_DAG IE_TYPE;

run;



proc datasets library=work nolist;

modify bef;

attrib _all_ label='';

quit;




proc sql;

create table list as select *

from list as a left join

bef as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




data alder1;

set bef;

keep pnr aar foed_dag;

if foed_dag~=.;

run;



data koen1;

set bef;

keep pnr aar koen;

if koen~='';

run;




proc sort data=alder1 nodupkey;

by pnr;

run;



proc sort data=koen1 nodupkey;

by pnr;

run;





proc datasets library=work nolist;

delete bef;

quit;



proc sort data=list nodupkey;

by lid;

run;





/**********************************************/
/* Add data on sex and age when it is missing */
/**********************************************/




/* Age */


data alder;

set alder;

byear=aar-alder;

run;



data alder;

set alder;

keep pnr byear;

run;



data alder1;

set alder1;

byear=year(foed_dag);

run;



data alder1;

set alder1;

keep pnr byear;

run;



data age;

set alder alder1;

if byear~=.;

run;



proc sort data=age nodupkey;

by pnr;

run;




proc sql;

create table list as select *

from list as a left join

age as b on

a.pnr=b.pnr;

quit;





data age;

set list;

keep pnr aar alder byear;

run;



data age;

set age;

length newalder $2;

newalder=PUT(aar-byear,2.0);

run;



data age;

set age;

length newalder1 $3;

zero='0';

newalder1=CATT(zero,newalder);

run;



data age;

set age;

keep pnr aar alder newalder1 newalder;

run;



data age;

set age;

alder_new=alder;

if alder='' and newalder~=' .' then alder_new=newalder1;

run;



data age;

set age;

keep pnr alder_new;

run;



proc sql;

create table list as select *

from list as a left join

age as b on

a.pnr=b.pnr;

quit;



data list;

set list;

if alder='' then alder=alder_new;

run;




data list;

set list;

drop alder_new;

run;



proc sort data=list nodupkey;

by lid;

run;




/* Sex */



data koen1;

set koen1;

length koen_new $1.;

koen_new=PUT(koen,1.0);

run;



data koen1;

set koen1;

drop koen;

run; 



data koen1;

set koen1;

rename koen_new=koen;

run;



data sex;

set koen koen1;

run;



data sex;

set sex;

if koen~='';

run;



proc sort data=sex nodupkey;

by pnr;

run;



data sex;

set sex;

keep pnr koen;

run;



data sex;

set sex;

rename koen=koen_new;

run;



proc sql;

create table list as select *

from list as a left join 

sex as b on

a.pnr=b.pnr;

quit;




data list;

set list;

if koen='' and koen_new~='' then koen=koen_new;

run;




data list;

set list;

drop koen_new foed_dag byear;

run;




proc sort data=list nodupkey;

by lid;

run;






/*****************/
/* Data from ind */
/*****************/




data ind;

set raw.ind;

keep pnr aar ERHVERVSINDK_13 koejd qaktivf qaktivf_ny05 NETOVSKUD NETOVSKUD_GL NETOVSKUD_13 PANTGAELD BANKGAELD OBLGAELD;

if aar<2003;

run;




proc datasets library=work nolist;

modify ind;

attrib _all_ label='';

quit;




proc sql;

create table list as select *

from list as a left join

ind as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete ind;

quit;




proc sort data=list nodupkey;

by lid;

run;





/******************/
/* Data from Indk */
/******************/



data indk;

set raw.indk;

keep pnr aar PRIGALD GALDIO;

if aar<2003;

run;




proc datasets library=work nolist;

modify indk;

attrib _all_ label='';

quit;




proc sql;

create table list as select *

from list as a left join

indk as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete indk;

quit;



proc sort data=list nodupkey;

by lid;

run;




/******************/
/* Data from fain */
/******************/



data fain;

set raw.fain;

keep pnr aar civst;

if aar<2003;

run;





proc datasets library=work nolist;

modify fain;

attrib _all_ label='';

quit;




proc sql;

create table list as select *

from list as a left join

fain as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;





proc datasets library=work nolist;

delete fain;

quit;




proc sort data=list nodupkey;

by lid;

run;










/************************/
/* Create new variables */
/************************/



/* Men */


data list;

set list;

male=0;

if koen=1 then male=1;

run; 



/* Home owners */


data list;

set list;

home=.;

if KOEJD=0 then home=0;

if KOEJD>0 and KOEJD~=. then home=1;

run;



/* Legal marriage */


data list;

set list;

legmarried=.;

if civst='G' then legmarried=1;

if civst~='G' and aar>1985 and DISPON_NY~=. then legmarried=0;

run;






/* Years of schooling */


data list;

set list;

schooling=put(HFPRIA,3.0)/12;

run;



data list;

set list;

schooling=ROUND(schooling,0.1);

run;
 





/* Assets, debt and wealth */


data list;

set list;

length AKTIV PASSIV FORMUE 8.0;


if aar<1997 then AKTIV=QAKTIVF;

if aar>1996 then AKTIV=QAKTIVF_NY05;


if aar<1997 then PASSIV=QPASSIV;

if aar>1996 then PASSIV=QPASSIVN;


FORMUE=AKTIV-PASSIV;

run;



/* Secured debt */


data list;

set list;

mortgage=.;

if aar<1995 then mortgage=PRIGALD;

if aar>1994 then mortgage=OBLGAELD;

run;








proc sort data=list nodupkey;

by lid;

run;





/***************************************************************************/
/* Adjust all nominal variables for inflation (use 2020 price level 104.5) */
/***************************************************************************/




data list;

set list;

cpi=.;

if aar='1983' then cpi=45.7;

if aar='1984' then cpi=48.6;

if aar='1985' then cpi=50.9;

if aar='1986' then cpi=52.8;

if aar='1987' then cpi=54.9;

if aar='1988' then cpi=57.4;

if aar='1989' then cpi=60.1;

if aar='1990' then cpi=61.7;

if aar='1991' then cpi=63.2;

if aar='1992' then cpi=64.5;

if aar='1993' then cpi=65.3;

if aar='1994' then cpi=66.6;

if aar='1995' then cpi=68.0;

if aar='1996' then cpi=69.5;

if aar='1997' then cpi=71.0;

if aar='1998' then cpi=72.3;

if aar='1999' then cpi=74.1;

if aar='2000' then cpi=76.2;

if aar='2001' then cpi=78.0;

if aar='2002' then cpi=79.9;

if aar='2003' then cpi=81.6;


run; 




data list;

set list;

DISPON_NY=DISPON_NY*(104.5/cpi);

ERHVERVSINDK_13=ERHVERVSINDK_13*(104.5/cpi);

KOEJD=KOEJD*(104.5/cpi);

LOENMV=LOENMV*(104.5/cpi);

AKTIV=AKTIV*(104.5/cpi);

PASSIV=PASSIV*(104.5/cpi);

FORMUE=FORMUE*(104.5/cpi);

NETOVSKUD=NETOVSKUD*(104.5/cpi); 

NETOVSKUD_GL=NETOVSKUD_GL*(104.5/cpi);  

NETOVSKUD_13=NETOVSKUD_13*(104.5/cpi); 

PRIGALD=PRIGALD*(104.5/cpi);  

GALDIO=GALDIO*(104.5/cpi);  

PANTGAELD=PANTGAELD*(104.5/cpi);  

BANKGAELD=BANKGAELD*(104.5/cpi);  

OBLGAELD=OBLGAELD*(104.5/cpi);  

mortgage_real=mortgage*(104.5/cpi);  

run;



data list;

set list;

DISPON_NY=ROUND(DISPON_NY,1);

ERHVERVSINDK_13=ROUND(ERHVERVSINDK_13,1);

KOEJD=ROUND(KOEJD,1);

LOENMV=ROUND(LOENMV,1);

AKTIV=ROUND(AKTIV,1);

PASSIV=ROUND(PASSIV,1);

FORMUE=ROUND(FORMUE,1);

NETOVSKUD=ROUND(NETOVSKUD,1); 

NETOVSKUD_GL=ROUND(NETOVSKUD_GL,1); 

NETOVSKUD_13=ROUND(NETOVSKUD_13,1);

PRIGALD=ROUND(PRIGALD,1); 

GALDIO=ROUND(GALDIO,1); 

PANTGAELD=ROUND(PANTGAELD,1); 

BANKGAELD=ROUND(BANKGAELD,1); 

OBLGAELD=ROUND(OBLGAELD,1); 

mortgage=ROUND(mortgage,1);

run;




/******************/
/* Winsorize data */
/******************/




data list;

set list;

newaar=input(aar,4.0);

run;



data stats;

set common.winsorize;

rename aar=newaar;

run;



proc sql;

create table list as select *

from list as a left join

stats as b on

a.newaar=b.newaar;

quit;




data list;

set list;


if Erhvervsindk_13 < p1_earned and Erhvervsindk_13~=. then Erhvervsindk_13=p1_earned;

if Erhvervsindk_13 > p99_earned and Erhvervsindk_13~=. then Erhvervsindk_13=p99_earned;


if  AKTIV< p1_aktiv and AKTIV~=. then AKTIV=p1_aktiv;

if  AKTIV> p99_aktiv and AKTIV~=. then AKTIV=p99_aktiv;


if PASSIV < p1_passiv and PASSIV~=. then PASSIV=p1_passiv;

if PASSIV > p99_passiv and PASSIV~=. then PASSIV=p99_passiv;


if FORMUE < p1_formue and FORMUE~=. then FORMUE=p1_formue;

if FORMUE > p99_formue and FORMUE~=. then FORMUE=p99_formue;


if KOEJD < p1_koejd and KOEJD~=. then KOEJD=p1_koejd;

if KOEJD > p99_koejd and KOEJD~=. then KOEJD=p99_koejd;


if PANTGAELD  < p1_PANTGAELD  and PANTGAELD ~=. then PANTGAELD =p1_PANTGAELD;

if PANTGAELD > p99_PANTGAELD  and PANTGAELD ~=. then PANTGAELD =p99_PANTGAELD;


if BANKGAELD< p1_BANKGAELD and BANKGAELD~=. then BANKGAELD=p1_BANKGAELD;

if BANKGAELD > p99_BANKGAELD and BANKGAELD~=. then BANKGAELD=p99_BANKGAELD;


if OBLGAELD < p1_OBLGAELD  and OBLGAELD ~=. then OBLGAELD =p1_OBLGAELD;

if  OBLGAELD > p99_OBLGAELD  and OBLGAELD ~=. then OBLGAELD =p99_OBLGAELD;


if  PRIGALD< p1_PRIGALD and PRIGALD~=. then PRIGALD=p1_PRIGALD;

if  PRIGALD> p99_PRIGALD and PRIGALD~=. then PRIGALD=p99_PRIGALD;


if  GALDIO< p1_GALDIO and GALDIO~=. then GALDIO=p1_GALDIO;

if  GALDIO> p99_GALDIO and GALDIO~=. then GALDIO=p99_GALDIO;


if  mortgage< p1_mortgage and mortgage~=. then mortgage=p1_mortgage;

if  mortgage> p99_mortgage and mortgage~=. then mortgage=p99_mortgage;


run;




proc sort data=list;

by pnr;

run;



data list;

set list;

drop newaar 

p99_earned p99_aktiv p99_passiv p99_formue p99_koejd p99_timelon p99_PANTGAELD p99_BANKGAELD p99_OBLGAELD p99_PRIGALD p99_GALDIO p99_mortgage

p1_earned p1_aktiv p1_passiv p1_formue p1_koejd p1_timelon p1_PANTGAELD p1_BANKGAELD p1_OBLGAELD p1_PRIGALD p1_GALDIO p1_mortgage;

run;





/******************************/
/* Create variables for table */
/******************************/



data automat;

set list;

age=input(alder,3.0);

run;





data automat;

set automat;


employed_pstill=0; 

unemployed_pstill=0; 

out_of_lf_pstill=0;


pstill_num=input(pstill,2.0);


if pstill_num=. then employed_pstill=.; 

if pstill_num=. then unemployed_pstill=.;

if pstill_num=. then out_of_lf_pstill=.;


if (pstill_num<38 and pstill_num~=.) or pstill_num in (71,72,73,74,75,76,77) then employed_pstill=1;

if pstill_num=40 then unemployed_pstill=1;

if (pstill_num>40 and pstill_num<58) or (pstill_num in (90,91,92,93,94,95,96,97,98)) then out_of_lf_pstill=1;


run;




/*************************************/
/* Save data for table on hard drive */
/*************************************/



proc sort data=automat nodupkey;

by lid;

run;



data common.table1_gp;

set automat;

run;




data automat;

set common.table1_gp;

run;




/**********************/
/* Compute statistics */
/**********************/





proc sort data=automat;

by dr;

run;



proc summary data=automat;

class dr;

var age male home legmarried schooling ANTBOERNF ANTPERSF DISPON_NY LOENMV ERHVERVSINDK_13 FORMUE AKTIV PASSIV KOEJD employed_pstill unemployed_pstill out_of_lf_pstill;

output out= tautomat

sum(male home legmarried)=sum_male sum_home sum_legmarried

sum(employed_pstill unemployed_pstill out_of_lf_pstill)=sum_employed_pstill sum_unemployed_pstill sum_out_of_lf_pstill

mean(age schooling ANTBOERNF ANTPERSF)=mean_age mean_schooling mean_children mean_people

mean(DISPON_NY LOENMV ERHVERVSINDK_13 FORMUE AKTIV PASSIV KOEJD)=mean_DISPON_NY mean_LOENMV mean_ERHVERVSINDK_13 mean_FORMUE mean_AKTIV mean_PASSIV mean_KOEJD

std(age schooling ANTBOERNF ANTPERSF)=std_age std_schooling std_children std_people

std(DISPON_NY LOENMV ERHVERVSINDK_13 FORMUE AKTIV PASSIV KOEJD)=std_DISPON_NY std_LOENMV std_ERHVERVSINDK_13 std_FORMUE std_AKTIV std_PASSIV std_KOEJD; 

run; 




/* Round numbers */


data tautomat;

set tautomat;

mean_DISPON_NY=ROUND(mean_DISPON_NY/1000,1); 

mean_LOENMV=ROUND(mean_LOENMV/1000,1); 

mean_ERHVERVSINDK_13=ROUND(mean_ERHVERVSINDK_13/1000,1);

mean_FORMUE=ROUND(mean_FORMUE/1000,1); 

mean_AKTIV=ROUND(mean_AKTIV/1000,1); 

mean_PASSIV=ROUND(mean_PASSIV/1000,1); 

mean_KOEJD=ROUND(mean_KOEJD/1000,1); 

std_DISPON_NY=ROUND(std_DISPON_NY/1000,1); 

std_LOENMV=ROUND(std_LOENMV/1000,1); 

std_ERHVERVSINDK_13=ROUND(std_ERHVERVSINDK_13/1000,1);

std_FORMUE=ROUND(std_FORMUE/1000,1); 

std_AKTIV=ROUND(std_AKTIV/1000,1); 

std_PASSIV=ROUND(std_PASSIV/1000,1); 

std_KOEJD=ROUND(std_KOEJD/1000,1); 

run;





data atabell;

length description N male_mean home_mean age_mean age_std legmarried_mean 

schooling_mean children_mean people_mean

DISPON_NY_mean LOENMV_mean ERHVERVSINDK_13_mean FORMUE_mean AKTIV_mean PASSIV_mean KOEJD_mean

employed_pstill_mean unemployed_pstill_mean out_of_lf_pstill_mean



$50;

set tautomat;

N=strip(put(_freq_,8.));


if dr=. then description="All";

if dr=1 then description="Applicants";

if dr=0 then description="Comparators";


male_mean=strip(put(100*sum_male/N,8.1));

home_mean=strip(put(100*sum_home/N,8.1));

legmarried_mean=strip(put(100*sum_legmarried/N,8.1));


employed_pstill_mean=strip(put(100*sum_employed_pstill/N,8.1)); 

unemployed_pstill_mean=strip(put(100*sum_unemployed_pstill/N,8.1)); 

out_of_lf_pstill_mean=strip(put(100*sum_out_of_lf_pstill/N,8.1));


age_mean=strip(put(mean_age,8.1));

age_std='(' || strip(put(std_age,8.1)) || ')';


schooling_mean=strip(put(mean_schooling,8.1)); 

schooling_std='(' || strip(put(std_schooling,8.1)) || ')';



children_mean=strip(put(mean_children,8.1)); 

children_std='(' || strip(put(std_children,8.1)) || ')';



people_mean=strip(put(mean_people,8.1));

people_std='(' || strip(put(std_people,8.1)) || ')';



DISPON_NY_mean=strip(put(mean_DISPON_NY,8.0)); 

DISPON_NY_std='(' || strip(put(std_DISPON_NY,8.0)) || ')';


LOENMV_mean=strip(put(mean_LOENMV,8.0)); 

LOENMV_std='(' || strip(put(std_LOENMV,8.0)) || ')';


ERHVERVSINDK_13_mean=strip(put(mean_ERHVERVSINDK_13,8.0)); 

ERHVERVSINDK_13_std='(' || strip(put(std_ERHVERVSINDK_13,8.0)) || ')';


FORMUE_mean=strip(put(mean_FORMUE,8.0)); 

FORMUE_std='(' || strip(put(std_FORMUE,8.0)) || ')';


AKTIV_mean=strip(put(mean_AKTIV,8.0)); 

AKTIV_std='(' || strip(put(std_AKTIV,8.0)) || ')';


PASSIV_mean=strip(put(mean_PASSIV,8.0)); 

PASSIV_std='(' || strip(put(std_PASSIV,8.0)) || ')';


KOEJD_mean=strip(put(mean_KOEJD,8.0)); 

KOEJD_std='(' || strip(put(std_KOEJD,8.0)) || ')';



run;




proc transpose data=atabell out=sluttabell;

var N male_mean age_mean age_std home_mean legmarried_mean schooling_mean schooling_std children_mean children_std people_mean people_std

DISPON_NY_mean DISPON_NY_std LOENMV_mean LOENMV_std ERHVERVSINDK_13_mean ERHVERVSINDK_13_std 

FORMUE_mean FORMUE_std AKTIV_mean AKTIV_std PASSIV_mean PASSIV_std employed_pstill_mean unemployed_pstill_mean out_of_lf_pstill_mean;

id description;

run;



/* Snyggar till tabellen */


proc datasets library=work nolist;

modify sluttabell;

attrib _all_ label='';

quit;



data sluttabell;

set sluttabell;

rename _name_=variable;

run;




data sluttabell;

set sluttabell;

length newvar $50;

if variable="N" then newvar="N";

if variable="male_mean" then newvar="Fraction men";

if variable="age_mean" then newvar="Mean age";


if variable="home_mean" then newvar="Fraction real-estate owners";

if variable="legmarried_mean" then newvar="Fraction legally married";


if variable="schooling_mean" then newvar="Mean years schooling";

if variable="people_mean" then newvar="Mean number persons";

if variable="children_mean" then newvar="Mean number children";

if variable="DISPON_NY_mean" then newvar="Mean disposable income";

if variable="LOENMV_mean" then newvar="Mean earnings";

if variable="ERHVERVSINDK_13_mean" then newvar="Mean earned income";


if variable="FORMUE_mean" then newvar="Mean wealth";

if variable="AKTIV_mean" then newvar="Mean assets";

if variable="PASSIV_mean" then newvar="Mean debt";

if variable="KOEJD_mean" then newvar="Mean real estate wealth";




if variable="employed_pstill_mean" then newvar="Fraction employed";

if variable="unemployed_pstill_mean" then newvar="Fraction unemployed ";

if variable="out_of_lf_pstill_mean" then newvar="Fraction out of labor force";


run;





data obs;

set sluttabell;

if variable='N';

run;



data age;

set sluttabell;

if variable='age_mean' or variable='age_std';

run;



data demo;

set sluttabell;

if variable='legmarried_mean' or variable='children_mean' or variable='children_std' or variable='people_mean' or variable='people_std'; 

run;



data school;

set sluttabell;

if variable='schooling_mean' or variable='schooling_std';

run;



data income;

set sluttabell;

if variable='DISPON_NY_mean' or variable='DISPON_NY_std' or variable='LOENMV_mean' or variable='LOENMV_std' 

or variable='ERHVERVSINDK_13_mean' or variable='ERHVERVSINDK_13_std' 

or variable="employed_pstill_mean" or variable="unemployed_pstill_mean" or variable="out_of_lf_pstill_mean"; 

run;



data wealth;

set sluttabell;

if variable='FORMUE_mean' or

variable='FORMUE_std' or

variable='AKTIV_mean' or

variable='AKTIV_std' or

variable='PASSIV_mean' or

variable='PASSIV_std' or

variable='KOEJD_mean' or

variable='KOEJD_std';

run;



data men;

set sluttabell;

if variable='male_mean';

run;



data home;

set sluttabell;

if variable='home_mean';

run;




data sluttabell;

set age men demo school home income wealth obs;

run;



proc datasets library=work nolist;

delete age men demo school home income wealth obs;

quit;



data sluttabell;

retain newvar;

set sluttabell;

drop variable;

run;





proc export data=sluttabell outfile='G:\Data\Workdata\707677\Common\03Output\03Tables\table1_comp.xls' dbms=excel5 replace;

run;





proc datasets library=work nolist kill;

quit;









